<!doctype html>
<html lang="en">

<head>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
  
  <meta name="generator" content="Hugo 0.98.0" />

  
  <meta name="description" content="走在通往幸福的路上">
  

  
  <link rel="apple-touch-icon" sizes="180x180" href="https://blog.v5u.win/apple-touch-icon.png">

  
  <link rel="icon" type="image/png" sizes="32x32" href="https://blog.v5u.win/favicon-32x32.png">

  
  <link rel="icon" type="image/png" sizes="16x16" href="https://blog.v5u.win/favicon-16x16.png">

  
  <link rel="manifest" href="https://blog.v5u.win/site.webmanifest">

  
  <link rel="mask-icon" href="https://blog.v5u.win/safari-pinned-tab.svg" color="">

  <meta name="msapplication-TileColor" content="">

  <meta name="theme-color" content="">

  
  <link rel="stylesheet" href="https://blog.v5u.win/css/bootstrap.min.css" />

  
  <title>sqlite3的使用 | 为吾优</title>
  

  <style>
body {
  min-width: 300px;
}

.custom-navbar {
  margin-bottom: 1em;
  height: 60px;
}

.custom-navbar a {
  display: inline-block; 
  padding: 18px 0;
  margin-right: 1em; 
  font-weight: bold; 
}

.custom-navbar a:hover,
.custom-navbar a:focus {
  text-decoration: none; 
}

@media print {
  .custom-navbar {
    display: none;
  }
}

article {
  padding-bottom: 1em;
}

img {
  max-width: 100%;
}


body {
  background-color: #fff;
}



body {
  color: #212529;
}



a {
  color: #007bff;
}



a:hover,
a:focus {
  color: #0056b3;
}



.custom-navbar {
  background-color: #212529;
}



.custom-navbar a {
  color: rgba(255,255,255,.75);
}



.custom-navbar a:hover,
.custom-navbar a:focus {
  color: rgba(255,255,255,1);
}



.container {
  max-width: 800px;
}





</style>
</head>

<body>
  <nav class="custom-navbar">
  <div class="container">
    
    <a href="/">文章</a>
    
    <a href="/tags/">标签</a>
    
    <a href="/about/">关于</a>
    
    <a href="/index.xml">RSS</a>
    
  </div>
</nav>
  
  <div class="container">
    <article>
      <h1>sqlite3的使用</h1>
<p><!-- raw HTML omitted --><!-- raw HTML omitted --><!-- raw HTML omitted -->新建数据库<!-- raw HTML omitted --></p>
<p><!-- raw HTML omitted -->sqlite3 databasefilename<!-- raw HTML omitted --></p>
<p><!-- raw HTML omitted -->检查databasefilename是否存在，如果不存在就创建并进入数据库（如果直接退出，数据库文件不会创建）  如果已经存在直接进入数据库 对数据库进行操作<!-- raw HTML omitted --></p>
<p><!-- raw HTML omitted --><!-- raw HTML omitted -->
<!-- raw HTML omitted --><!-- raw HTML omitted --></p>
<p><!-- raw HTML omitted -->创建表<!-- raw HTML omitted -->：</p>
<p><!-- raw HTML omitted -->create table if not exists UserList(id integer primary key autoincrement,firstName[not null],lastName[not null],sex,birthday,remark[not null],resumeNum[not null])<!-- raw HTML omitted --></p>
<p><!-- raw HTML omitted --><!-- raw HTML omitted --></p>
<p>该表已经创建了主键，可以自增ID</p>
<p><!-- raw HTML omitted --><!-- raw HTML omitted --></p>
<p>terminal命令行使用sqlite3</p>
<p>打开或创建：进入到文件所在目录下，输入sqlite3 filename</p>
<p>1、显示数据库中所有的表：.tables</p>
<p><!-- raw HTML omitted --><!-- raw HTML omitted --></p>
<p>2、显示表头：.head on 然后执行 select * from tableName</p>
<p>sqlite中命令:
以.开头,大小写敏感（数据库对象名称是大小写不敏感的）
.exit
.help 查看帮助 针对命令
.database 显示数据库信息；包含当前数据库的位置
.tables 或者 .table 显示表名称  没有表则不显示
.schema 命令可以查看创建数据对象时的SQL命令；
.schema databaseobjectname查看创建该数据库对象时的SQL的命令；如果没有这个数据库对象就不显示内容，不会有错误提示</p>
<p>.read FILENAME 执行指定文件中的SQL语句
.headers on/off  显示表头 默认off</p>
<p>.mode list|column|insert|line|tabs|tcl|csv   改变输出格式，具体如下</p>
<p>sqlite&gt; .mode list
sqlite&gt; select * from emp;
7369|SMITH|CLERK|7902|17-12-1980|800||20
7499|ALLEN|SALESMAN|7698|20-02-1981|1600|300|30
如果字段值为NULL 默认不显示 也就是显示空字符串</p>
<p>sqlite&gt; .mode column
sqlite&gt; select * from emp;
7369        SMITH       CLERK       7902        17-12-1980  800                     20        
7499        ALLEN       SALESMAN    7698        20-02-1981  1600        300         30        
7521        WARD        SALESMAN    7698        22-02-1981  1250        500         30 </p>
<p>sqlite&gt; .mode insert
sqlite&gt; select * from dept;
INSERT INTO table VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO table VALUES(20,'RESEARCH','DALLAS');
INSERT INTO table VALUES(30,'SALES','CHICAGO');
INSERT INTO table VALUES(40,'OPERATIONS','BOSTON');</p>
<p>sqlite&gt; .mode line
sqlite&gt; select * from dept;
DEPTNO = 10
 DNAME = ACCOUNTING
   LOC = NEW YORK</p>
<p>DEPTNO = 20
 DNAME = RESEARCH
   LOC = DALLAS</p>
<p>DEPTNO = 30
 DNAME = SALES
   LOC = CHICAGO</p>
<p>DEPTNO = 40
 DNAME = OPERATIONS
   LOC = BOSTON</p>
<p>sqlite&gt; .mode tabs
sqlite&gt; select * from dept;
10<!-- raw HTML omitted --> <!-- raw HTML omitted -->ACCOUNTING<!-- raw HTML omitted --> <!-- raw HTML omitted --> NEW YORK
20<!-- raw HTML omitted --> <!-- raw HTML omitted -->RESEARCH<!-- raw HTML omitted --> <!-- raw HTML omitted --> DALLAS
30<!-- raw HTML omitted --> <!-- raw HTML omitted -->SALES<!-- raw HTML omitted --> <!-- raw HTML omitted --> CHICAGO
40<!-- raw HTML omitted --> <!-- raw HTML omitted -->OPERATIONS<!-- raw HTML omitted --> <!-- raw HTML omitted --> BOSTON</p>
<p>sqlite&gt; .mode tcl
sqlite&gt; select * from dept;
&quot;10&quot;<!-- raw HTML omitted --> <!-- raw HTML omitted -->&quot;ACCOUNTING&quot;<!-- raw HTML omitted --><!-- raw HTML omitted -->&quot;NEW YORK&quot;<!-- raw HTML omitted --><!-- raw HTML omitted -->
&quot;20&quot;<!-- raw HTML omitted --> <!-- raw HTML omitted -->&quot;RESEARCH&quot;<!-- raw HTML omitted --><!-- raw HTML omitted -->&quot;DALLAS&quot;<!-- raw HTML omitted --><!-- raw HTML omitted -->
&quot;30&quot;<!-- raw HTML omitted --> <!-- raw HTML omitted -->&quot;SALES&quot;<!-- raw HTML omitted --> <!-- raw HTML omitted --> &quot;CHICAGO&quot;<!-- raw HTML omitted --> <!-- raw HTML omitted -->
&quot;40&quot;<!-- raw HTML omitted --> <!-- raw HTML omitted -->&quot;OPERATIONS&quot;<!-- raw HTML omitted --><!-- raw HTML omitted -->&quot;BOSTON&quot;<!-- raw HTML omitted --><!-- raw HTML omitted --></p>
<p>sqlite&gt; .mode csv
sqlite&gt; select * from dept;
10,ACCOUNTING,&quot;NEW YORK&quot;
20,RESEARCH,DALLAS
30,SALES,CHICAGO
40,OPERATIONS,BOSTON</p>
<p>.separator &quot;X&quot; 更改分界符号为X
sqlite&gt; .separator '<strong>'  
sqlite&gt; select * from dept;
10</strong>ACCOUNTING**&quot;NEW YORK&quot;
20<strong>RESEARCH</strong>DALLAS
30<strong>SALES</strong>CHICAGO
40<strong>OPERATIONS</strong>BOSTON</p>
<p>.dump ?TABLE?            生成形成数据库表的SQL脚本
.dump 生成整个数据库的脚本在终端显示
.output stdout           将输出打印到屏幕  默认
.output filename  将输出打印到文件（.dump  .output 结合可将数据库以sql语句的形式导出到文件中）
.nullvalue STRING        查询时用指定的串代替输出的NULL串 默认为.nullvalue ''</p>
<p>字段类型：</p>
<p>数据库中存储的每个值都有一个类型,都属于下面所列类型中的一种,(被数据库引擎所控制)
NULL: 这个值为空值
INTEGER: 值被标识为整数,依据值的大小可以依次被存储为1,2,3,4,5,6,7,8个字节
REAL: 所有值都是浮动的数值,被存储为8字节的IEEE浮动标记序号.
TEXT: 文本. 值为文本字符串,使用数据库编码存储(TUTF-8, UTF-16BE or UTF-16-LE).
BLOB: 值是BLOB数据,如何输入就如何存储,不改变格式.</p>
<p>值被定义为什么类型只和值自身有关,和列没有关系,和变量也没有关系.所以sqlite被称作 弱类型 数据库
数据库引擎将在执行时检查、解析类型，并进行数字存储类型(整数和实数)和文本类型之间的转换.
SQL语句中部分的带双引号或单引号的文字被定义为文本,
如果文字没带引号并没有小数点或指数则被定义为整数,
如果文字没带引号但有小数点或指数则被定义为实数, 
如果值是空则被定义为空值.
BLOB数据使用符号X'ABCD'来标识.</p>
<p>但实际上，sqlite3也接受如下的数据类型： 
smallint 16位的整数。 
interger 32位的整数。 
decimal(p,s) 精确值p是指全部有几个十进制数,s是指小数点后可以有几位小数。如果没有特别指定，则系统会默认为p=5 s=0 。 
float  32位元的实数。 
double  64位元的实数。 </p>
<p>char(n)  n 长度的字串，n不能超过 254。 
varchar(n) 长度不固定且其最大长度为 n 的字串，n不能超过 4000。 
graphic(n) 和 char(n) 一样，不过其单位是两个字节， n不能超过127。这个形态是为了支持两个字节长度的字体，如中文字。 
vargraphic(n) 可变长度且其最大长度为n的双字元字串，n不能超过2000 
date  包含了 年份、月份、日期。 
time  包含了 小时、分钟、秒。 
timestamp 包含了 年、月、日、时、分、秒、千分之一秒。</p>
<p>SQLite包含了如下时间/日期函数： 
datetime() 产生日期和时间    无参数表示获得当前时间和日期
sqlite&gt; select datetime();
2012-01-07 12:01:32
有字符串参数则把字符串转换成日期
sqlite&gt; select datetime('2012-01-07 12:01:30'); 
2012-01-07 12:01:30</p>
<p>select date('2012-01-08','+1 day','+1 year');
2013-01-09</p>
<p>select datetime('2012-01-08 00:20:00','+1 hour','-12 minute');
2012-01-08 01:08:00</p>
<p>select datetime('now','start of year');
2012-01-01 00:00:00</p>
<p>select datetime('now','start of month');
2012-01-01 00:00:00</p>
<p>select datetime('now','start of day');
2012-01-08 00:00:00</p>
<p>select datetime('now','start of week');错误</p>
<p>select datetime('now','localtime');
结果：2006-10-17 21:21:47</p>
<p>date()产生日期    
sqlite&gt; select date('2012-01-07 12:01:30'); 
2012-01-07
同理 有参和无参
select date('now','start of year');
2012-01-01</p>
<p>select date('2012-01-08','+1 month');
2012-02-08</p>
<p>time() 产生时间 
select time();
03:14:30</p>
<p>select time('23:18:59');
23:18:59</p>
<p>select time('23:18:59','start of day');
00:00:00</p>
<p>select time('23:18:59','end of day');错误</p>
<p>在时间/日期函数里可以使用如下格式的字符串作为参数： 
YYYY-MM-DD 
YYYY-MM-DD HH:MM 
YYYY-MM-DD HH:MM:SS 
YYYY-MM-DD HH:MM:SS.SSS 
HH:MM 
HH:MM:SS 
HH:MM:SS.SSS 
now 
其中now是产生现在的时间。</p>
<p>日期不能正确比较大小,会按字符串比较，日期默认格式 dd-mm-yyyy
select hiredate from emp order by hiredate;</p>
<p>17-11-1981
17-12-1980
19-04-1987
20-02-1981
22-02-1981</p>
<p>strftime() 对以上三个函数产生的日期和时间进行格式化
strftime()函数可以把YYYY-MM-DD HH:MM:SS格式的日期字符串转换成其它形式的字符串。 strftime(格式, 日期/时间, 修正符, 修正符, …)  select strftime('%d',datetime());
它可以用以下的符号对日期和时间进行格式化： 
%d 在该月中的第几天, 01-31 
%f 小数形式的秒，SS.SSS 
%H 小时, 00-23 
%j 算出某一天是该年的第几天，001-366 
%m 月份，00-12 
%M 分钟, 00-59 
%s 从1970年1月1日到现在的秒数 
%S 秒, 00-59 
%w 星期, 0-6 (0是星期天) 
%W 算出某一天属于该年的第几周, 01-53 
%Y 年, YYYY 
%% 百分号</p>
<p>select strftime('%Y.%m.%d %H:%M:%S','now'); 
select strftime('%Y.%m.%d %H:%M:%S','now','localtime'); 
结果：2006.10.17 21:41:09</p>
<p>select hiredate from emp 
order by strftime('%Y.%m.%d %H:%M:%S',hiredate); 正确</p>
<p>select strftime('%Y.%m.%d %H:%M:%S',hiredate) from emp 
order by strftime('%Y.%m.%d %H:%M:%S',hiredate); 错误</p>
<p>算术函数 
abs(X) 返回给定数字表达式的绝对值。 
max(X,Y[,&hellip;]) 返回表达式的最大值。  组函数 max(列名)
sqlite&gt; select max(2,3,4,5,6,7,12);
12</p>
<p>min(X,Y[,&hellip;]) 返回表达式的最小值。 
random() 返回随机数。
sqlite&gt; select random();
3224224213599993831
 
round(X[,Y]) 返回数字表达式并四舍五入为指定的长度或精度。 </p>
<p>字符处理函数 
length(X) 返回给定字符串表达式的字符个数。 
lower(X) 将大写字符数据转换为小写字符数据后返回字符表达式。 
upper(X) 返回将小写字符数据转换为大写的字符表达式。 
substr(X,Y,Z) 返回表达式的一部分。  从Y开始读Z个字符  Y最小值1
sqlite&gt; select substr('abcdef',3,3);            
cde</p>
<p>quote(A) 给字符串加引号
 sqlite&gt; select quote('aaa');
'aaa'</p>
<p>条件判断函数 
ifnull(X,Y)  如果X为null 返回Y
select ifnull(comm,0) from emp;
0
300
500
0
1400</p>
<p>集合函数 
avg(X) 返回组中值的平均值。 
count(X) 返回组中项目的数量。 
max(X) 返回组中值的最大值。 
min(X) 返回组中值的最小值。 
sum(X) 返回表达式中所有值的和。 </p>
<p>其他函数 
typeof(X) 返回数据的类型。 
sqlite&gt; select typeof(111);
integer
sqlite&gt; select typeof('233');
text
sqlite&gt; select typeof('2012-12-12');
text
sqlite&gt; select typeof('223.44');
text
sqlite&gt; select typeof(223.44);
real</p>
<p>last_insert_rowid() 返回最后插入的数据的ID。 
sqlite_version() 返回SQLite的版本。 
sqlite&gt; select sqlite_version();
3.7.9</p>
<p>change_count() 返回受上一语句影响的行数。 
last_statement_change_count()</p>
<p>create table emp_bak select * from EMP;不能在sqlite中使用</p>
<p>插入记录
insert into table_name values (field1, field2, field3&hellip;);
查询
select * from table_name;查看table_name表中所有记录；
select * from table_name where field1='xxxxx'; 查询符合指定条件的记录；</p>
<p>select &hellip;.. <!-- raw HTML omitted --><!-- raw HTML omitted -->
from table_name[,table_name2,&hellip;]
where &hellip;..<!-- raw HTML omitted --> <!-- raw HTML omitted -->
group by&hellip;.<!-- raw HTML omitted --> <!-- raw HTML omitted -->
having &hellip;.<!-- raw HTML omitted --> <!-- raw HTML omitted -->
order by &hellip;</p>
<p>select &hellip;.. <!-- raw HTML omitted --><!-- raw HTML omitted -->
from table_name  inner join | left outer join | right outer join table_name2
on &hellip;
where &hellip;..<!-- raw HTML omitted --> <!-- raw HTML omitted -->
group by&hellip;.<!-- raw HTML omitted --> <!-- raw HTML omitted -->
having &hellip;.<!-- raw HTML omitted --> <!-- raw HTML omitted -->
order by &hellip;</p>
<p>子查询：
select * 
from EMP m
where SAL&gt;
(select avg(SAL) from EMP where DEPTNO=m.DEPTNO);<!-- raw HTML omitted --> <!-- raw HTML omitted --> </p>
<p>支持case when then 语法
update EMP
set SAL=
(
case
when DEPTNO=10 and JOB='MANAGER' then SAL<em>1.1
when DEPTNO=20 and JOB='CLERK' then SAL</em>1.2
when DEPTNO=30  then SAL<em>1.1
when DEPTNO=40  then SAL</em>1.2
else SAL
END
);</p>
<p>select ENAME, 
case DEPTNO
when 10 then '后勤部'
when 20 then '财务部'
when 30 then '内务部门'
else '其他部门'
end as dept
from EMP;</p>
<p>支持关联子查询  in后面的语法中可以有limit（mysql不可以）
select *
from emp e
where e.EMPNO in 
(
select empno  
from EMP
where deptno=e.DEPTNO
order by SAL desc
limit 0,2
);</p>
<p>支持表和表之间的数据合并等操作
union 去重复  union all 不去掉重复
select deptno from emp
union 
select deptno from dept;</p>
<p>select deptno from emp
union all
select deptno from dept;</p>
<p>在列名前加distinct也是去重复
sqlite&gt; select distinct deptno from emp;</p>
<p>删除
delete from table_name where &hellip;</p>
<p>删除表
drop table_name;     删除表；
drop index_name;     删除索引；</p>
<p>修改
update table_name
set xxx=value[, xxx=value,&hellip;]
where &hellip;</p>
<p>建立索引</p>
<p>如果资料表有相当多的资料，我们便会建立索引来加快速度。好比说：</p>
<p>create index film_title_index on film(title);
意思是针对film资料表的name字段，建立一个名叫film_name_index的索引。这个指令的语法为</p>
<p>CREATE [ UNIQUE ]  NONCLUSTERED  INDEX index_name
    ON { table | view } ( column [ ASC | DESC ] [ ,&hellip;n ] )
create index index_name on table_name(field_to_be_indexed);
一旦建立了索引，sqlite3会在针对该字段作查询时，自动使用该索引。这一切的操作都是在幕后自动发生的，无须使用者特别指令。</p>
<p>其他sqlite的特别用法</p>
<p>sqlite可以在shell底下直接执行命令：
sqlite3 film.db &quot;select * from emp;&quot;</p>
<p>输出 HTML 表格：
sqlite3 -html film.db &quot;select * from film;&quot;
将数据库「倒出来」：</p>
<p>sqlite3 film.db &quot;.dump&quot; &gt; output.sql
利用输出的资料，建立一个一模一样的数据库（加上以上指令，就是标准的SQL数据库备份了）：</p>
<p>sqlite3 film.db &lt; output.sql
在大量插入资料时，你可能会需要先打这个指令：</p>
<p>begin;
插入完资料后要记得打这个指令，资料才会写进数据库中：
commit;</p>
<p>sqlite&gt; begin;
sqlite&gt; insert into aaaa values('aaa','333');
sqlite&gt; select * from aaaa;
2|sdfds
sdfsd|9
2012-12-12|13:13:13
aaa|333
sqlite&gt; rollback;
sqlite&gt; select * from aaaa;
2|sdfds
sdfsd|9
2012-12-12|13:13:13</p>
<p>创建和删除视图
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
DROP VIEW view_name</p>
<p>create view  e as
select avg(SAL) avgsal,DEPTNO
from EMP
group by DEPTNO;</p>
<p>select ENAME,EMP.DEPTNO,SAL,avgsal
from EMP inner join e
on EMP.DEPTNO=e.DEPTNO
where SAL&gt;avgsal;</p>
<p>练习员工表：</p>
<p>PRAGMA foreign_keys=OFF;
BEGIN TRANSACTION;
CREATE TABLE DEPT
(
DEPTNO int(2) not null,
DNAME varchar(14),
LOC    varchar(13)
);
INSERT INTO &quot;DEPT&quot; VALUES(10,'ACCOUNTING','NEW YORK');
INSERT INTO &quot;DEPT&quot; VALUES(20,'RESEARCH','DALLAS');
INSERT INTO &quot;DEPT&quot; VALUES(30,'SALES','CHICAGO');
INSERT INTO &quot;DEPT&quot; VALUES(40,'OPERATIONS','BOSTON');
CREATE TABLE EMP
(
EMPNO    int(4) not null,
ENAME    varchar(10),
JOB      varchar(9),
MGR      int(4),
HIREDATE date,
SAL      int(7 ),
COMM     int(7 ),
DEPTNO   int(2)
);
INSERT INTO &quot;EMP&quot; VALUES(7369,'SMITH','CLERK',7902,'17-12-1980',800,NULL,20);
INSERT INTO &quot;EMP&quot; VALUES(7499,'ALLEN','SALESMAN',7698,'20-02-1981',1600,300,30);
INSERT INTO &quot;EMP&quot; VALUES(7521,'WARD','SALESMAN',7698,'22-02-1981',1250,500,30);
INSERT INTO &quot;EMP&quot; VALUES(7566,'JONES','MANAGER',7839,'02-04-1981',2975,NULL,20);
INSERT INTO &quot;EMP&quot; VALUES(7654,'MARTIN','SALESMAN',7698,'28-09-1981',1250,1400,30);
INSERT INTO &quot;EMP&quot; VALUES(7698,'BLAKE','MANAGER',7839,'01-05-1981',2850,NULL,30);
INSERT INTO &quot;EMP&quot; VALUES(7782,'CLARK','MANAGER',7839,'09-06-1981',2450,NULL,10);
INSERT INTO &quot;EMP&quot; VALUES(7788,'SCOTT','ANALYST',7566,'19-04-1987',3000,NULL,20);
INSERT INTO &quot;EMP&quot; VALUES(7839,'KING','PRESIDENT',NULL,'17-11-1981',5000,NULL,10);
INSERT INTO &quot;EMP&quot; VALUES(7844,'TURNER','SALESMAN',7698,'08-09-1981',1500,0,30);
INSERT INTO &quot;EMP&quot; VALUES(7876,'ADAMS','CLERK',7788,'23-05-1987',1100,NULL,20);
INSERT INTO &quot;EMP&quot; VALUES(7900,'JAMES','CLERK',7698,'03-12-1981',950,NULL,30);
INSERT INTO &quot;EMP&quot; VALUES(7902,'FORD','ANALYST',7566,'03-12-1981',3000,NULL,20);
INSERT INTO &quot;EMP&quot; VALUES(7934,'MILLER','CLERK',7782,'23-01-1982',1300,NULL,10);
CREATE TABLE SALGRADE
(
GRADE int,
LOSAL int,
HISAL int
);
INSERT INTO &quot;SALGRADE&quot; VALUES(1,700,1200);
INSERT INTO &quot;SALGRADE&quot; VALUES(2,1201,1400);
INSERT INTO &quot;SALGRADE&quot; VALUES(3,1401,2000);
INSERT INTO &quot;SALGRADE&quot; VALUES(4,2001,3000);
INSERT INTO &quot;SALGRADE&quot; VALUES(5,3001,9999);
COMMIT;</p>

    </article>
  </div>

  
  
  

  
</body>

</html>